package studio.raptor.dispatcher.server;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.junit.BeforeClass;
import org.junit.Test;
import studio.raptor.databus.command.EventMetaData;
import studio.raptor.dispatcher.server.sort.CsipC2uEvents;
import studio.raptor.dispatcher.server.sort.H2DatabaseHelper;

/**
 * @author jack
 * @since 1.0
 */
public class AutoPrepareTestingEnv {

  protected static Connection connection;
  HikariDataSource ds = null;

  public static List<EventMetaData> events = Lists.newArrayList();

  public static Map<String, CsipC2uEvents> csipC2uEvents = Maps.newHashMap();
  public static Map<String, Map<String, List<Long>>> csipC2uParams = Maps.newHashMap();


  @BeforeClass
  public static void setup() throws SQLException, IOException {
    H2DatabaseHelper h2DatabaseHelper = new H2DatabaseHelper("~", "usb ", "root", "root", 2);
    h2DatabaseHelper.prepareH2Database();

    connection = h2DatabaseHelper.tryToGetConnection();
    connection.setAutoCommit(false);
    buildEvents();
  }





  @Test
  public void testTransfer() throws SQLException {

    init();

    String query_event = "SELECT * FROM csip_c2u_events_new ORDER BY event_id";
    String query_params = "SELECT * FROM csip_c2u_paras_new WHERE event_id = ?";

    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(query_event);
    while (rs.next()) {
      String eventId = rs.getString("event_id").substring(3);
      String objectKey = rs.getString("object_key");
      Long areaId = rs.getLong("area_id");
      Long partyId = rs.getLong("party_id");
      Long prodId = rs.getLong("prod_id");
      String orderTypeId = rs.getString("order_type_id");

      EventMetaData eventMetaData = new EventMetaData();
      eventMetaData.setBusinessNo(objectKey);
      eventMetaData.setRegionId(areaId);
      eventMetaData.setCustId(Lists.newArrayList(partyId).toArray(new Long[1]));
      eventMetaData.setProdInstId(Lists.newArrayList(prodId).toArray(new Long[1]));
      eventMetaData.setOrderTypeIds(orderTypeId);

      insertTrace(eventId, objectKey);

      insertMeta(eventId, objectKey, orderTypeId);

      List<Long> acctIds = new ArrayList<>();
      List<Long> offers = new ArrayList<>();
      List<Long> offer2ProdIds = new ArrayList<>();
      PreparedStatement pstmt = connection.prepareStatement(query_params);

      pstmt = connection.prepareStatement(query_params);
      pstmt.setString(1, rs.getString("event_id"));
      ResultSet parasRs = pstmt.executeQuery();

      while (parasRs.next()) {
        String name = parasRs.getString("para_name");
        if ("OFFERS".equals(name)) {
          Long offerId = parasRs.getLong("para_value");
          offers.add(offerId);
        } else if ("OFFERS2PRODID".equals(name)) {
          Long offer2Prod = parasRs.getLong("para_value");
          offer2ProdIds.add(offer2Prod);

        } else if ("ACCOUNT_ID".equals(name)) {
          Long acctId = parasRs.getLong("para_value");
          acctIds.add(acctId);
        }
      }

      eventMetaData.setOfferInstId(offers.toArray(new Long[offers.size()]));
      eventMetaData.setAcctId(acctIds.toArray(new Long[acctIds.size()]));
      eventMetaData.setOfferProdId(offer2ProdIds.toArray(new Long[offer2ProdIds.size()]));
      insertParam(eventId, eventMetaData);
      connection.commit();

    }
  }

  private void init() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:oracle:thin:@192.168.199.24:1521:orcla");
    config.setUsername("crm");
    config.setPassword("crm123");
    config.addDataSourceProperty("cachePrepStmts", "true");
    config.addDataSourceProperty("prepStmtCacheSize", "250");
    config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

    ds = new HikariDataSource(config);
  }

  private void insertTrace(String eventId, String objectKey) throws SQLException {
    String event_trace =
        "INSERT INTO event_trace (trace_id,object_key,code,status,topic,retry_times,resp_code)"
            + " VALUES (?,?,'0025214000000046','w','ord_biz',0,-1)";
    Connection conn = ds.getConnection();
    PreparedStatement tracePstmt = null;
    try {
      tracePstmt = conn.prepareStatement(event_trace);
      tracePstmt.setString(1, eventId);
      tracePstmt.setString(2, objectKey);
      tracePstmt.execute();
      conn.commit();
    } finally {
      if (tracePstmt != null) {
        tracePstmt.close();
      }

      conn.close();
    }
  }

  private void insertMeta(String eventId, String objectKey, String orderTypeId)
      throws SQLException {
    String event_meta =
        "INSERT INTO event_meta (meta_id,event_seq,event_name,event_source_cd,deliver_time,object_key,event_type,status,order_type_id)"
            + " VALUES (?,10,'ord_biz','11',to_date(?, 'yyyy-mm-dd hh24:mi:ss'),?,1,'w',?)";
    Connection conn = ds.getConnection();
    PreparedStatement metaPstmt = null;

    try {
      metaPstmt = conn.prepareStatement(event_meta);
      metaPstmt.setString(1, eventId);
      metaPstmt.setString(2, "2017-06-08 05:33:49");
      metaPstmt.setString(3, objectKey);
      metaPstmt.setString(4, orderTypeId);
      metaPstmt.execute();
      conn.commit();

    } finally {
      if (metaPstmt != null) {
        metaPstmt.close();
      }
      conn.close();
    }
  }

  private void insertParam(String eventId, EventMetaData metaData) throws SQLException {
    String event_params = "INSERT INTO event_params(meta_id,param_name,param_value) VALUES(?,?,?)";
    Connection conn = ds.getConnection();
    PreparedStatement metaPstmt = null;

    try {
      metaPstmt = conn.prepareStatement(event_params);
      Long[] acctIds = metaData.getAcctId();
      if (acctIds != null) {
        for (Long acctId : acctIds) {
          metaPstmt.setString(1, eventId);
          metaPstmt.setString(2, "acctId");
          metaPstmt.setString(3, String.valueOf(acctId));
          metaPstmt.addBatch();
        }
      }
      metaPstmt.executeBatch();

      Long[] custIds = metaData.getCustId();
      if (custIds != null) {
        for (Long custId : custIds) {
          if (custId != 0l) {
            metaPstmt.setString(1, eventId);
            metaPstmt.setString(2, "custId");
            metaPstmt.setString(3, String.valueOf(custId));
            metaPstmt.addBatch();
          }
        }
      }
      metaPstmt.executeBatch();
      Long[] prodIds = metaData.getProdInstId();
      if (prodIds != null) {
        for (Long prodId : prodIds) {
          if (prodId != 0) {
            metaPstmt.setString(1, eventId);
            metaPstmt.setString(2, "custId");
            metaPstmt.setString(3, String.valueOf(prodId));
            metaPstmt.addBatch();
          }
        }
      }
      metaPstmt.executeBatch();

      Long[] offerProdIds = metaData.getOfferProdId();
      if (offerProdIds != null) {
        for (Long offerProdId : offerProdIds) {
          metaPstmt.setString(1, eventId);

          metaPstmt.setString(2, "offerProdId");
          metaPstmt.setString(3, String.valueOf(offerProdId));
          metaPstmt.addBatch();
        }
      }
      metaPstmt.executeBatch();

      Long[] offerIds = metaData.getOfferInstId();
      if (offerIds != null) {
        for (Long offerId : offerIds) {
          metaPstmt.setString(1, eventId);

          metaPstmt.setString(2, "offerId");
          metaPstmt.setString(3, String.valueOf(offerId));
          metaPstmt.addBatch();
        }
      }
      metaPstmt.executeBatch();
      conn.commit();

    } finally {
      if (metaPstmt != null) {
        metaPstmt.close();
      }
      conn.close();
    }

  }

  private static void buildEvents() throws SQLException {
    String query_event = "select * from csip_c2u_events_new order by event_id";
    String query_params = "select * from csip_c2u_paras_new where event_id = ?";

    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(query_event);
    while (rs.next()) {
      EventMetaData event = new EventMetaData();
      CsipC2uEvents csipC2uEvent = new CsipC2uEvents();
      String evnetId = rs.getString("event_id");
      String objectKey = rs.getString("object_key");
      csipC2uEvent.setEventId(objectKey);

      event.setRegionId(rs.getLong("area_id"));

      Long partyId  = rs.getLong("party_id");
      csipC2uEvent.setPartyId(partyId);

      event.setBusinessNo(objectKey);

      Long prodId = rs.getLong("prod_id");
      csipC2uEvent.setProdId(prodId);


      csipC2uEvent.setOrderTypeId(rs.getString("order_type_id"));
      event.setOrderTypeIds(rs.getString("order_type_id"));

      PreparedStatement pstmt = connection.prepareStatement(query_params);
      pstmt.setString(1,evnetId);

      ResultSet parasRs = pstmt.executeQuery();
      List<Long> acctIds = new ArrayList<>();
      List<Long> offers = new ArrayList<>();
      List<Long> offer2ProdIds = new ArrayList<>();
      Map<String, List<Long>> paras = Maps.newHashMap();

      while (parasRs.next()) {
        String name = parasRs.getString("para_name");
        if ("OFFERS".equals(name)) {

          offers.add(parasRs.getLong("para_value"));
        } else if ("OFFERS2PRODID".equals(name)) {

          offer2ProdIds.add(parasRs.getLong("para_value"));
        } else if ("ACCOUNT_ID".equals(name)) {

          acctIds.add(parasRs.getLong("para_value"));
        }
        paras.put("OFFERS", offers);
        paras.put("OFFERS2PRODID", offer2ProdIds);
        paras.put("ACCOUNTS", acctIds);
      }
      event.setOfferInstId(offers.toArray(new Long[offers.size()]));
      event.setAcctId(acctIds.toArray(new Long[acctIds.size()]));
      event.setOfferProdId(offer2ProdIds.toArray(new Long[offer2ProdIds.size()]));
      if(null != prodId && 0l!=prodId) {
        event.setProdInstId(Lists.newArrayList(prodId).toArray(new Long[1]));
      }
      if(null != partyId && 0l!=partyId) {
        event.setCustId(Lists.newArrayList(partyId).toArray(new Long[1]));
      }

      csipC2uParams.put(csipC2uEvent.getEventId(), paras);
      events.add(event);
      csipC2uEvents.put(csipC2uEvent.getEventId(), csipC2uEvent);

      pstmt.close();

    }
    stmt.close();

  }


}
